<?php
class ScoutMsgInfo extends AppModel {
	var $name = "ScoutMsgInfo";
	var $useTable = "scout_msg_info";
	var $primaryKey = 'id';
	var $belongsTo = array(
			'ThemaInfo'=>array(
					'className'=>'ThemaInfo', 'conditions'=>"", 'order'=>'',
					'foreignKey'=>'thema_id'
			),
			'CompanyAccount'=>array(
					'className'=>'CompanyAccount', 'conditions'=>"", 'order'=>'',
					'foreignKey'=>'id'
			)
	);
	
	function doDelete($id) {
		$sql = "update scout_msg_info ScoutMsgInfo set deleted=now() " . " where ScoutMsgInfo.id in (".Sanitize::sql($id).")";
		$this->query($sql);
	}
	
	function getScoutMsgInfo($scoutId) {
		// EDIT tsubasa 2012/12/24 start
		$sql = "SELECT 
					sr.id as srid,sr.status, 
					msg.id,msg.company_account_id,
					scout_order.company_id, 	
					sr.received,  
					msg.subject, 
					replace(msg.contents,'%id%',sr.member_id || '') as contents, 
					case ac.role 
					 when 'System' then 'カモメ編集室'
					 else ac.name end as name, 
					cm.company_name 
					,ac.role AS role 
				FROM scout_msg_reaction_info as sr 
				JOIN scout_msg_info as msg  
				ON sr.scout_id = msg.id  
				AND sr.deleted is null AND sr.member_deleted is null   
				JOIN company_account_mst as ac ON msg.company_account_id = ac.id 
				AND ac.deleted is null AND msg.deleted is null 	 		
			    join scout_order_info as scout_order 
			     on scout_order.id = sr.scout_order_id				
				JOIN company_info as cm ON scout_order.company_id = cm.id 
				AND cm.deleted is null
				WHERE sr.id =". $scoutId ;
		// EDIT tsubasa 2012/12/24 end
		$msgRs = $this->query($sql);
		return $msgRs[0][0];
	}
	
}

?>